ASP.NET Core Blazor | Data from two tables in DataGrid

In this video we will discuss how to retrieve and display data from two or more related tables in a DatGrid.

Database table relationships

The following are the different types of relationships between database tables.

1. One to One
2. One to Many
3. Many to Many

Consider the following 3 tables - Employees, Salaries and Departments

• We have a one-to-one relationship between Employees and Salaries and Employees and Departments tables.
• DeptId in Employees table is a foreign key referencing Id column in Departments table
• SalaryId in Employees table is a foreign key referencing Id column in Salaries table

Displaying foreign key data in datagrid

• We wan to display DepartmentName in the DataGrid along with employee firstname, lastname etc.
• DepartmentName is present in the Departments table and it is linked to the Employees table through the foreign key DeptId.
• So to display DepartmentName in the DataGrid we use <GridForeignColumn> component.

<GridForeignColumn ForeignDataSource="@Departments" HeaderText="Dept Name" 
                   Field=@nameof(Employee.DepartmentId)
                   ForeignKeyValue="DepartmentName">
</GridForeignColumn>

• ForeignDataSource - Defines the foreign data.
• Field - Defines the mapping column name to the foreign data.
• ForeignKeyValue - Defines the display field from the foreign data.

Complete Example (All CRUD operations supported)

@page "/"
@*@page "/datagridfk"*@
@using Syncfusion.Blazor.Grids
@using Syncfusion.Blazor.DropDowns

<SfGrid @ref="employeeGrid" DataSource="@Employees"
        Toolbar="@(new List<string>() { "Add", "Edit", "Update", "Delete", "Cancel" })">
    <GridEditSettings AllowAdding="true" AllowEditing="true" AllowDeleting="true"></GridEditSettings>
    <GridEvents OnActionBegin="ActionBeginHandler" TValue="Employee"></GridEvents>
    <GridColumns>
        <GridColumn Field=@nameof(Employee.EmployeeId) IsPrimaryKey="true" HeaderText="ID" Width="60"></GridColumn>
        <GridColumn Field=@nameof(Employee.FirstName) HeaderText="First Name"></GridColumn>
        <GridColumn Field=@nameof(Employee.LastName) HeaderText="Last Name"></GridColumn>
        <GridColumn Field=@nameof(Employee.DateOfBrith) HeaderText="Date of Birth"></GridColumn>
        <GridColumn Field=@nameof(Employee.Email) HeaderText="Email"></GridColumn>
        <GridColumn Field=@nameof(Employee.Gender) HeaderText="Gender" Width="140px">
            <EditTemplate>
                <SfDropDownList ID="Gender" TItem="string" TValue="Gender"
                                @bind-Value="@((context as Employee).Gender)"
                                DataSource="@(Enum.GetNames(typeof(Gender)))">
                </SfDropDownList>
            </EditTemplate>
        </GridColumn>
        <GridForeignColumn Field=@nameof(Employee.DepartmentId)
                           HeaderText="Dept Name" Width="150"
                           ForeignKeyValue="DepartmentName" ForeignDataSource="@Departments">
        </GridForeignColumn>
    </GridColumns>
</SfGrid>

@code{

    public List<Employee> Employees { get; set; }

    public List<Department> Departments { get; set; }

    public SfGrid<Employee> employeeGrid { get; set; }

    [Inject]
    public IEmployeeService EmployeeService { get; set; }

    [Inject]
    public IDepartmentService DepartmentService { get; set; }

    protected override async Task OnInitializedAsync()
    {
        Employees = (await EmployeeService.GetAllEmployees()).ToList();
        Departments = (await DepartmentService.GetAllDepartments()).ToList();
    }

    public async void ActionBeginHandler(ActionEventArgs<Employee> Args)
    {
        if (Args.RequestType.Equals(Syncfusion.Blazor.Grids.Action.Save))
        {
            if (Args.Action == "Add")
            {
                await EmployeeService.AddEmployee(Args.Data);
                Employees = (await EmployeeService.GetAllEmployees()).ToList();
                employeeGrid.Refresh();
            }
            else
            {
                await EmployeeService.UpdateEmployee(Args.Data);
            }
        }
        if (Args.RequestType.Equals(Syncfusion.Blazor.Grids.Action.Delete))
        {
            await EmployeeService.DeleteEmployee(Args.Data.EmployeeId);
        }
    }
}